# -*- coding: utf-8 -*-
import xlrd
import xlwt

import sqlite3
import pprint

conn = sqlite3.connect(":memory:", detect_types=sqlite3.PARSE_DECLTYPES|sqlite3.PARSE_COLNAMES)
cursor = conn.cursor()
cursor.execute(' CREATE TABLE dmtable(id varchar(255) NOT NULL PRIMARY KEY,\
        type varchar(255),\
        subtype varchar(255),\
        itemaddr varchar(255),\
        itemtype varchar(255),\
        description varchar(255))');
        
book=xlrd.open_workbook("dm.xls")
sh=book.sheet_by_index(0)

for i in range(2,sh.nrows):
    id = sh.cell_value(i,0).strip()
    type = sh.cell_value(i,1).strip()
    subtype = sh.cell_value(i,2).strip()
    itemaddr = sh.cell_value(i,3).strip()
    itemtype = sh.cell_value(i,4).strip()
    description = sh.cell_value(i,5).strip()
    if itemaddr.strip()!='':
        insert_str = "insert into dmtable values('%s','%s','%s','%s','%s','%s')"%(id,type,subtype,itemaddr,itemtype,description)
        pprint.pprint( insert_str)
        cursor.execute(insert_str)
        conn.commit()         

book_rd=xlrd.open_workbook("new_version.xls")
sh_rd=book_rd.sheet_by_index(0)

wb_wt = xlwt.Workbook()
ws_wt = wb_wt.add_sheet('ruleDtail')

print "the dm_out rows is ",sh_rd.nrows

for row in range(0,sh_rd.nrows):
    id = sh_rd.cell_value(row,0).strip()
    type = sh_rd.cell_value(row,1).strip()
    subtype = sh_rd.cell_value(row,2).strip()
    itemaddr = sh_rd.cell_value(row,3).strip()
    itemtype = sh_rd.cell_value(row,4).strip()
    description = sh_rd.cell_value(row,5).strip()
    ws_wt.write(row, 0, id)
    ws_wt.write(row, 1, type)
    ws_wt.write(row, 2, subtype)
    query_str = "select itemaddr,itemtype,description from dmtable where type = '%s' and subtype = '%s' "%(type,subtype)
    query_list = cursor.execute(query_str)
    cnt = 0
    for item in query_list:
        cnt = cnt + 1               
        ws_wt.write(row, 3, item[0])
        ws_wt.write(row, 4, item[1])
        ws_wt.write(row, 5, item[2])
    if cnt == 0:
        ws_wt.write(row, 3, itemaddr)
        ws_wt.write(row, 4, itemtype)
        ws_wt.write(row, 5, description)
            
wb_wt.save("output.xls")
conn.close()

print "finish"